set define off

CREATE OR REPLACE PROCEDURE data_dump (
-- ---------------------------------------------------------------------------------------------------
--
-- Script:      data_dump.sql
--
-- Author:      Adrian Billington
--              www.oracle-developer.net
--              (Modified by Jon Heller to work with LOBs, and support CSV format)
--
-- Description: A standalone procedure to dump the results of a query to delimited flat-file. This 
--              utility supports Oracle 8i upwards.
--
--              Note that the dynamic code that is built to perform the data dump can optionally be 
--              written to a separate file. 
--
-- Usage:       Usage is quite simple. A dynamic query is passed in as a parameter. As this uses 
--              DBMS_SQL to parse the SQL, all expressions must have an alias.
--
--              a) Dump the contents of a table
--                 ----------------------------
--
--                 BEGIN
--                    data_dump( query_in     => 'SELECT * FROM table_name',
--                               file_in      => 'table_name.csv',
--                               directory_in => 'LOG_DIR',
--                               delimiter_in => ',' );
--                 END;
--                 /
--
--              b) Use an expression in the query
--                 ------------------------------
--
--                 BEGIN
--                    data_dump( query_in     => 'SELECT ''LITERAL'' AS alias_name FROM table_name',
--                               file_in      => 'table_name.csv',
--                               directory_in => 'LOG_DIR',
--                               delimiter_in => ',' );
--                 END;
--                 /
--
--             See list of parameters for the various other options available.
-- 
-- ---------------------------------------------------------------------------------------------------
                            query_in             IN VARCHAR2,
                            file_in              IN VARCHAR2,
                            directory_in         IN VARCHAR2,
                            nls_date_fmt_in      IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS',
                            write_action_in      IN VARCHAR2 DEFAULT 'WB',
                            array_size_in        IN PLS_INTEGER DEFAULT 1000,
                            delimiter_in         IN VARCHAR2 DEFAULT NULL,
                            surrounding_char_in  in varchar2 default null,
                            display_header_in    in boolean default true,
                            convert_HTML_in      in boolean default false,
                            dump_code_in         IN BOOLEAN DEFAULT FALSE) AUTHID CURRENT_USER IS

   v_fh           UTL_FILE.FILE_TYPE;
   v_ch           BINARY_INTEGER      := DBMS_SQL.OPEN_CURSOR;
   v_sql          VARCHAR2(32767)     := query_in;
   v_dir          VARCHAR2(512)       := directory_in;
   v_outfile      VARCHAR2(128)       := file_in;
   v_sqlfile      VARCHAR2(128)       := file_in||'.sql';
   v_arr_size     PLS_INTEGER         := array_size_in;
   v_col_cnt      PLS_INTEGER         := 0;
   v_delimiter    VARCHAR2(1)         := NULL;
   v_write_action VARCHAR2(2)         := write_action_in;
   v_nls_date_fmt VARCHAR2(30)        := nls_date_fmt_in;
   v_dummy        NUMBER;
   v_type         VARCHAR2(8);
   t_describe     DBMS_SQL.DESC_TAB;
   --varchar2s provides an unlimited size for the overall SQL, but each line
   --is limited to 255 characters.
   t_plsql        DBMS_SQL.VARCHAR2S;
   --Overall file size is smaller, but it's fine for my needs
   v_plsql        varchar2(32767);
   eol            varchar2(1)         := chr(10);
   headerRow      varchar2(32767);

   /* Procedure to output code for debug and assign plsql variable... */
   PROCEDURE put (
             string_in IN VARCHAR2
             ) IS
   BEGIN
      IF dump_code_in THEN
         UTL_FILE.PUT_LINE(v_fh,string_in);
      END IF;
      --t_plsql(t_plsql.COUNT + 1) := string_in;
      v_plsql := v_plsql||string_in||eol;
   --Exception when others then raise just hides the error.
   --EXCEPTION
   --   WHEN OTHERS THEN
   --      RAISE;
   END put;

BEGIN

   /* Open the file that the dynamic PL/SQL will be written to for debug... */
   IF dump_code_in THEN
      v_fh := UTL_FILE.FOPEN(v_dir, v_sqlfile, 'W');
   END IF;
	
   /* Parse the query that will be used to fetch all the data to be written out... */
   DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE);

   /* Now describe the dynamic SQL to analyze the number of columns in the query... */
   DBMS_SQL.DESCRIBE_COLUMNS(v_ch, v_col_cnt, t_describe);
  
   /* Now begin the dynamic PL/SQL... */
   put('DECLARE');
   put('   v_fh UTL_FILE.FILE_TYPE;');
   --Custom: Need larger varchar sizes
   put('   type varchar2_ntt is table of varchar2(32767);');
   --NCLOB works better than CLOB for me, since CLOB uses 4 bytes per character and NCLOB uses 2.
   put('   type clob_ntt is table of clob;');
   put('   chunkSize number := 5000;');
   put('   clobLength number;');
   put('   CURSOR cur_sql IS');
   put('      '||REPLACE(v_sql,'"','''''')||';');

   /* Now loop through the describe table to declare arrays in the dynamic PL/SQL... */
   FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
      headerRow := headerRow||delimiter_in||t_describe(i).col_name;

      IF t_describe(i).col_type = 2 THEN
         v_type := 'NUMBER';
         put('   '||t_describe(i).col_name||' DBMS_SQL.'||v_type||'_TABLE;');
      ELSIF t_describe(i).col_type = 12 THEN
         v_type := 'DATE';
         put('   '||t_describe(i).col_name||' DBMS_SQL.'||v_type||'_TABLE;');
      --CLOBS.  I'm not sure how many characters will work, hopefully 32,767.
      ELSIF t_describe(i).col_type in (8,112) THEN
         put('   '||t_describe(i).col_name||' clob_ntt;');
      ELSE
         v_type := 'VARCHAR2';
         --Custom: need larger varchar sizes
         put('   '||t_describe(i).col_name||' varchar2_ntt;');
      END IF;
      --put('   '||t_describe(i).col_name||' DBMS_SQL.'||v_type||'_TABLE;');
   END LOOP;

   --Add function for converting HTML codes
   --NOTE: Quotes are returned as double-quotes.  So this is really meant for a CSV export.
   put('');
   put(q'!  function replaceHTMLCodes(v_string in varchar2) return varchar2 is!');
   put(q'!  begin!');
   put(q'!    return replace(replace(replace(replace(replace( v_string, !');
   put(q'!      '&lt;', '<'), '&gt;', '>'), '&apos;', ''''), '&quot;', '""'), '&amp;', '&');!');
   put(q'!  end replaceHTMLCodes;!');
   put('');

   /* Syntax to set the date format to preserve time in the output, open the out file and start to collect... */
   put('BEGIN');
   put('   EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = '''''||v_nls_date_fmt||''''''';');
   --put('   v_fh := UTL_FILE.FOPEN('''||v_dir||''','''||v_outfile||''','''||v_write_action||''');');
   --Custom: Increase line size to 32767
   put('   v_fh := UTL_FILE.FOPEN('''||v_dir||''','''||v_outfile||''','''||v_write_action||''', 32767);');
   put('   OPEN cur_sql;');

   --Print a header row with the column names.
   if display_header_in then
      put('   utl_file.put_raw(v_fh, utl_raw.cast_to_raw('''||substr(headerRow, 2)||'''||chr(10)));');
   end if;

   put('   LOOP');
   put('      FETCH cur_sql');

   IF t_describe.COUNT > 1 THEN

      put('      BULK COLLECT INTO '||t_describe(t_describe.FIRST).col_name||',');

      /* Add all other arrays into the fetch list except the last... */
      FOR i IN t_describe.FIRST + 1 .. t_describe.LAST - 1 LOOP  
         put('                        '||t_describe(i).col_name||',');
      END LOOP;

      /* Add in the last array and limit... */
      put('                        '||t_describe(t_describe.LAST).col_name||' LIMIT '||v_arr_size||';');

   ELSE
      /* Just output the one collection and LIMIT... */
      put('      BULK COLLECT INTO '||t_describe(t_describe.FIRST).col_name||' LIMIT '||v_arr_size||';');

   END IF;

   /* Now add syntax to loop though the fetched array and write out the values to file... */
   put('      IF '||t_describe(t_describe.FIRST).col_name||'.COUNT > 0 THEN');
   put('         FOR i IN '||t_describe(t_describe.FIRST).col_name||'.FIRST .. '||
                                  t_describe(t_describe.FIRST).col_name||'.LAST LOOP');

   FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
      --LOBs
      if t_describe(i).col_type in (8,112) then

         --Print the CLOB as raw.
         put('            UTL_FILE.PUT_RAW(v_fh,utl_raw.cast_to_raw('''||v_delimiter||surrounding_char_in||'''));');
         put('            for j in 1 .. ceil(nvl(length('||t_describe(i).col_name||'(i)), 0) / chunkSize) loop ');

         if convert_HTML_in then
            put('              utl_file.put_raw(v_fh,utl_raw.cast_to_raw(replaceHTMLCodes(substr('||t_describe(i).col_name||'(i), (j-1)*chunkSize+1, chunkSize))));');
         else
            put('              utl_file.put_raw(v_fh,utl_raw.cast_to_raw(substr('||t_describe(i).col_name||'(i), (j-1)*chunkSize+1, chunkSize)));');
         end if;

         put('            end loop; ');
         put('            UTL_FILE.PUT_RAW(v_fh,utl_raw.cast_to_raw('''||surrounding_char_in||'''));');

         --Convert clob to varchar2, then put.  (I'm not sure why this works, but it allows for more characters.)
         /*
         put('            tempstring := '||t_describe(i).col_name||'(i);');
         put('            UTL_FILE.PUT_RAW(v_fh,utl_raw.cast_to_raw('''||v_delimiter||surrounding_char_in||''' || tempstring || '''||surrounding_char_in||'''));');
         v_delimiter := NVL(delimiter_in,',');        
         */
      --Everything but LOBs
      else
         if convert_HTML_in then
            put('            UTL_FILE.PUT_RAW(v_fh,utl_raw.cast_to_raw('''||v_delimiter||surrounding_char_in||'''||replaceHTMLCodes('||t_describe(i).col_name||'(i))||'''||surrounding_char_in||'''));');
         else
            put('            UTL_FILE.PUT_RAW(v_fh,utl_raw.cast_to_raw('''||v_delimiter||surrounding_char_in||'''||'||t_describe(i).col_name||'(i)||'''||surrounding_char_in||'''));');
         end if;
         v_delimiter := NVL(delimiter_in,',');        
      end if;

      
   END LOOP;

   /* Add a new line marker into the file and move on to next record... */
   --For write binary, cannot use new_line, must print character instead.
   put('            utl_file.put_raw(v_fh,utl_raw.cast_to_raw(chr(10)));');
   --put('            UTL_FILE.NEW_LINE(v_fh);');
   put('         END LOOP;');

   /* Complete the IF statement... */
   put('      END IF;');

   /* Add in an EXIT condition and complete the loop syntax... */
   put('      EXIT WHEN cur_sql%NOTFOUND;');
   put('   END LOOP;');
   put('   CLOSE cur_sql;');
   put('   UTL_FILE.FCLOSE(v_fh);');

   /* Add in some exception handling... */
   put('EXCEPTION');
   put('   WHEN UTL_FILE.INVALID_PATH THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid path.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INVALID_MODE THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid mode.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INVALID_OPERATION THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid operation.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INVALID_FILEHANDLE THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - invalid filehandle.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.WRITE_ERROR THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - write error.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.READ_ERROR THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - read error.'');');
   put('      RAISE;');
   put('   WHEN UTL_FILE.INTERNAL_ERROR THEN');
   put('      DBMS_OUTPUT.PUT_LINE(''Error - internal error.'');');
   put('      RAISE;');
   put('   WHEN OTHERS THEN');
   put('      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255));');
   put('      RAISE;');
   put('END;');

   /* Now close the cursor and sql file... */
   DBMS_SQL.CLOSE_CURSOR(v_ch);
   IF dump_code_in THEN
      UTL_FILE.FCLOSE(v_fh);
   END IF;
  
   /*
    * Execute the t_plsql collection to dump the data. Use DBMS_SQL as we have a collection
    * of syntax...
    */
   v_ch := DBMS_SQL.OPEN_CURSOR;
   --DBMS_SQL.PARSE(v_ch, t_plsql, t_plsql.FIRST, t_plsql.LAST, TRUE, DBMS_SQL.NATIVE);
  DBMS_SQL.PARSE(v_ch, v_plsql, DBMS_SQL.NATIVE);

   v_dummy := DBMS_SQL.EXECUTE(v_ch);
   DBMS_SQL.CLOSE_CURSOR(v_ch);

EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid path.');
      RAISE;
   WHEN UTL_FILE.INVALID_MODE THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid mode.');
      RAISE;
   WHEN UTL_FILE.INVALID_OPERATION THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid operation.');
      RAISE;
   WHEN UTL_FILE.INVALID_FILEHANDLE THEN
      DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.');
      RAISE;
   WHEN UTL_FILE.WRITE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - write error.');
      RAISE;
   WHEN UTL_FILE.READ_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - read error.');
      RAISE;
   WHEN UTL_FILE.INTERNAL_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error - internal error.');
      RAISE;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,255));
      dbms_output.put_line('Error stack: '||dbms_utility.format_error_stack);
      dbms_output.put_line('Error backtrace: '||dbms_utility.format_error_backtrace);
      RAISE;

--CREATE OR REPLACE PUBLIC SYNONYM data_dump FOR data_dump;
--GRANT EXECUTE ON data_dump TO PUBLIC;
END;
/
